import csv,os,shutil,function
import mysql.connector
from datetime import datetime

def read_csv_file(file_path):
    arr=[]
    i=0
    with open(file_path, mode='r', encoding='utf-8') as csvfile:
        csv_reader = csv.reader(csvfile)
        for row in csv_reader:
            if i>0:
                value=row[2]+row[1]
                arr.append(value.replace('\\','/'))
            i+=1
    return arr

mydb = mysql.connector.connect(
  host="192.168.10.81",
  user="root",
  passwd="",
  database="xiaocheng"
)
# 获取最大id
mycursor = mydb.cursor()
mycursor.execute("select max(id) from data_model") 
myresult = mycursor.fetchone()
begin_id=myresult[0]+1


# 读取数据
csv_path = 'D:/Desk/repeat.csv'
repeat_paths=read_csv_file(csv_path)
pic_path = 'D:/Desk/系统图库/model/'
pic_paths = function.get_file_paths(pic_path)

now = datetime.now()
formatted_date = now.strftime("%Y-%m-%d")

datas=[]
i=0
for pic_path in pic_paths:
    file_name=os.path.basename(pic_path)
    pic_path=pic_path.replace('\\','/')
    if pic_path not in repeat_paths:
        dict=[] 
        pic_path=pic_path.replace('\\','/')
        name='DB' +str(begin_id + 1000 + i)
        pic=name+os.path.splitext(file_name)[1]
        new_path="Y:/pic/model/"+pic
        shutil.copy(pic_path,new_path)     
        dict.append(name)
        dict.append(pic)
        dict.append(pic_path)
        dict.append(pic_path.split('/')[4])
        if pic_path.split('/')[4]=='全球女装':
            topic=pic_path.split('/')[5].split('#')[0]
            dict.append(topic)      
            dict.append(pic_path.split('/')[6])        
            dict.append(formatted_date)        
            dict.append("镜像添加")
            style_name=pic_path.split('/')[5].split('#')[1]+'#'+pic_path.split('/')[6]
            dict.append(style_name)
        if pic_path.split('/')[4]=='全球男装':
            topic=pic_path.split('/')[5].split('#')[0]
            style_name=pic_path.split('/')[5].split('#')[1]+'#'+pic_path.split('/')[6]
            dict.append(topic.split('-')[1])      
            dict.append(pic_path.split('/')[6])        
            dict.append(formatted_date)        
            dict.append("镜像添加")
            dict.append(style_name)
        if pic_path.split('/')[4]=='全球童装':
            topic=pic_path.split('/')[5].split('#')[0]
            dict.append(topic)      
            dict.append(pic_path.split('/')[6])        
            dict.append(formatted_date)        
            dict.append("镜像添加")
            style_name=pic_path.split('/')[5].split('#')[1]+'#'+pic_path.split('/')[6]
            dict.append(style_name)
        if pic_path.split('/')[4]=='亚洲女装':
            dict.append('默认')
            dict.append(pic_path.split('/')[6])        
            dict.append(formatted_date)        
            dict.append("镜像添加")
            a=pic_path.split('/')[5]
            style_name=a.split('-')[1]+'#'+pic_path.split('/')[6]
            dict.append(style_name)
        datas.append(tuple(dict))
        i+=1
        print(i)
print(len(datas))



# 插入数据
sql = "insert into data_model (name,pic,path,category,topic,color,date,add_sort,style_name) values (%s,%s,%s,%s,%s,%s,%s,%s,%s)" 
mycursor.executemany(sql, datas) 
mydb.commit()    # 数据表内容有更新，必须使用到该语句
 
print(mycursor.rowcount, "记录插入成功。")

mycursor.close()
mydb.close()